package com.apes.fn.server.workDesk.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @program: apes-server
 * @description:
 * @author: czs
 * @create: 2022-07-29 10:14
 **/
@Component
public class WorkDeskConfigDao extends ApesDao {

    /**
     * 查询未完结事项
     * -入参:DEPT/POST
     * -按单据类型/门店分组
     * -获取每份数量
     * @param param
     * @return
     */
    public JSONArray querySystemUseCheckOrderCountByDept(JSONObject param) {
        String sSQL = "select ORDER_TYPE,nvl(DEPT_NAME,'NONE') DEPT_NAME,nvl (DEPT_ID,'NONE')DEPT_ID, count, f.OTHER,F.DATA FORM_ID, F.API API\n" +
                "from (select A.ORDER_TYPE, A.DEPT_ID,A.DEPT_NAME, count(*) count\n" +
                "      from V_UNCOMPLETED_ORDER A\n" +
                "      group by A.ORDER_TYPE, A.DEPT_ID,A.DEPT_NAME) E,\n" +
                "     WORK_DESK_CONFIG F\n" +
                "where f.TYPE = 'unfinished_work'\n" +
                "  and f.OLD is not null\n" +
                "  and e.ORDER_TYPE = f.OLD\n";
        if(param.containsKey("DEPT")) {
            String deptSQL = "  select d.DEPT_ID\n" +
                    "                  from MAS_DEPT A,\n" +
                    "                       MAS_STORE b,\n" +
                    "                       mer_merchant C,\n" +
                    "                       MAS_STORE D\n" +
                    "                  where A.ID = B.DEPT_ID\n" +
                    "                    AND B.MERCHANT_ID = C.ID\n" +
                    "                    AND d.MERCHANT_ID = c.ID\n" +
                    "                    and d.DEPT_ID <> :DEPT\n" +
                    "                    AND A.ID = :DEPT\n" +
                    "                    and 1 = (select MERCHANT_MANAGEMENT\n" +
                    "                             from AUT_POSTS p\n" +
                    "                             where p.id = :POST)\n" +
                    "                  union all\n" +
                    "                  select :DEPT\n" +
                    "                  from DUAL";
            List<Map> maps = find(deptSQL, param);
            if (maps.size() == 1){
                sSQL += "  and DEPT_ID = '" + maps.get(0).get("DEPT_ID") + "'";
            } else if(maps.size() > 1){
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < maps.size(); i++) {
                    String deptId = (String) maps.get(i).get("DEPT_ID");
                    if (i == maps.size() - 1 ){
                        sb.append("'" + deptId + "'");
                    } else {
                        sb.append("'" + deptId + "',");
                    }
                }
                sSQL += "  and DEPT_ID in ("+ sb.toString() + ")";
            }
        }

        return toJSONArray(find(sSQL, param));
    }

    /**
     * 工作台-待出方案
     * -入参:DEPT_ID
     * @return
     */
    public JSONArray workDeskWaitPlan(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append(" select A.CAR_LICENSE,\n" +
                "        d.REALNAME,\n" +
                "        (select f.DESCRIPTION\n" +
                "         from fn_pick_up_type i,\n" +
                "              mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = e.ID) type,\n" +
                "        MILEAGE,\n" +
                "        (select f.DESCRIPTION from MAS_ENUMERATION f where f.id = fuel_meter) fuel_meter,\n" +
                "        key_number,\n" +
                "        (select B.NAME\n" +
                "         from MAS_PARTY_ROLE A,\n" +
                "              MAS_PARTY b\n" +
                "         where A.ID = e.CREATOR_ID\n" +
                "           and PARTY_ID = b.ID) || '/' || TO_CHAR(e.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info\n" +
                " FROM VEHICLE_INFO_COMBINE A\n" +
                "          left join FSM_FLOW_INST B on A.ID = B.BUSINESS_KEY\n" +
                "          left join FSM_STATE C on B.CURRENT_STATE = C.ID\n" +
                "          left join FN_CUSTOMER_EXPAND d on a.FN_CUSTOMER_EXPAND_ID = d.ID\n" +
                "          left join FN_PICK_UP_CAR e on A.PICK_CAR_ID = e.ID\n" +
                " where A.END = 0\n" +
                "   AND B.BUSINESS_TYPE = 'VehicleInfo'\n" +
                "   AND C.STARTED = 0\n" +
                "   AND C.FINISH = 0\n" +
                "   AND B.FSM_FLOW_ID = '000320'\n" +
                "   AND C.FSM_FLOW_ID = '000320'\n" +
                "   and (SELECT Y.ID\n" +
                "        FROM FSM_FLOW_INST X,\n" +
                "             FSM_STATE Y\n" +
                "        WHERE X.BUSINESS_KEY = A.ID\n" +
                "          AND X.CURRENT_STATE = Y.ID\n" +
                "          AND X.FSM_FLOW_ID = '000321'\n" +
                "          AND X.FINISHED = 0\n" +
                "          AND Y.STARTED = 0\n" +
                "          AND ROWNUM = 1) = :LABEL_TWO");
                if(param.containsKey("DEPT_ID")) sf.append("   and A.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }


    /**
     * 工作台-未结算交车
     * @return
     */
    public JSONArray workDeskNotSettled(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("select F.ID,\n" +
                "       A.CAR_LICENSE,\n" +
                "       d.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(F.STATE) STATE,\n" +
                "       h.NAME,\n" +
                "       g.SALE_QTY,\n" +
                "       g.PRICE_UNIT,\n" +
                "       g.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = f.BELONG_DEPT_ID)dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "        from fn_pick_up_type i,\n" +
                "             mas_enumeration f\n" +
                "        where i.TYPE = f.ID\n" +
                "          and i.PICK_UP_CAR_ID = e.ID)                                       type,\n" +
                "       (select B.NAME\n" +
                "        from MAS_PARTY_ROLE A,\n" +
                "             MAS_PARTY b\n" +
                "        where A.ID = e.CREATOR_ID\n" +
                "          and PARTY_ID = b.ID) || '/' || TO_CHAR(e.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(f.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss') CREATE_DATE\n" +
                "FROM VEHICLE_INFO_COMBINE A\n" +
                "         left join FSM_FLOW_INST B on A.ID = B.BUSINESS_KEY\n" +
                "         left join FSM_STATE C on B.CURRENT_STATE = C.ID\n" +
                "         left join FN_CUSTOMER_EXPAND d on a.FN_CUSTOMER_EXPAND_ID = d.ID\n" +
                "         left join FN_PICK_UP_CAR e on A.PICK_CAR_ID = e.ID\n" +
                "         left join SER_SERVICE_PLAN F on F.FN_CUSTOMER_EXPAND_ID = D.ID\n" +
                "         left join SER_SERVICE_PLAN_ITEM G on G.SERVICE_PLAN_ID = f.ID\n" +
                "         left join MAS_PRODUCT h on h.id = g.PRODUCT_ID\n" +
                "where A.END = 0\n" +
                "  AND B.BUSINESS_TYPE = 'VehicleInfo'\n" +
                "  AND F.STATE NOT IN ('termination')\n" +
                "  AND C.STARTED = 0\n" +
                "  AND C.FINISH = 0\n" +
                "  AND B.FSM_FLOW_ID = '000320'\n" +
                "  AND C.FSM_FLOW_ID = '000320'\n" +
                "  and (SELECT Y.ID\n" +
                "       FROM FSM_FLOW_INST X,\n" +
                "            FSM_STATE Y\n" +
                "       WHERE X.BUSINESS_KEY = A.ID\n" +
                "         AND X.CURRENT_STATE = Y.ID\n" +
                "         AND X.FSM_FLOW_ID = '000320'\n" +
                "         AND X.FINISHED = 0\n" +
                "         AND Y.STARTED = 0\n" +
                "         AND ROWNUM = 1) = :LABEL_TWO");
        if(param.containsKey("DEPT_ID")) sf.append("  and A.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }

    /**
     * 工作台-待施工
     * -入参:DEPT_ID
     * @return
     */
    public JSONArray workDeskWaitStartWork(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("select F.ID,\n" +
                "       A.CAR_LICENSE,\n" +
                "       d.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(F.STATE) STATE,\n" +
                "       h.NAME,\n" +
                "       g.SALE_QTY,\n" +
                "       g.PRICE_UNIT,\n" +
                "       g.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = f.BELONG_DEPT_ID)dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "        from fn_pick_up_type i,\n" +
                "             mas_enumeration f\n" +
                "        where i.TYPE = f.ID\n" +
                "          and i.PICK_UP_CAR_ID = e.ID)                                       type,\n" +
                "       (select B.NAME\n" +
                "        from MAS_PARTY_ROLE A,\n" +
                "             MAS_PARTY b\n" +
                "        where A.ID = e.CREATOR_ID\n" +
                "          and PARTY_ID = b.ID) || '/' || TO_CHAR(e.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(f.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss') CREATE_DATE\n" +
                "FROM VEHICLE_INFO_COMBINE A\n" +
                "         left join FSM_FLOW_INST B on A.ID = B.BUSINESS_KEY\n" +
                "         left join FSM_STATE C on B.CURRENT_STATE = C.ID\n" +
                "         left join FN_CUSTOMER_EXPAND d on a.FN_CUSTOMER_EXPAND_ID = d.ID\n" +
                "         left join FN_PICK_UP_CAR e on A.PICK_CAR_ID = e.ID\n" +
                "         left join SER_SERVICE_PLAN F on F.FN_CUSTOMER_EXPAND_ID = D.ID\n" +
                "         left join SER_SERVICE_PLAN_ITEM G on G.SERVICE_PLAN_ID = f.ID\n" +
                "         left join MAS_PRODUCT h on h.id = g.PRODUCT_ID\n" +
                "where A.END = 0\n" +
                "  AND B.BUSINESS_TYPE = 'VehicleInfo'\n");
                SQLCondition(sf,param);
        sf.append("  AND C.STARTED = 0\n" +
                "  AND C.FINISH = 0\n" +
                "  AND B.FSM_FLOW_ID = '000320'\n" +
                "  AND C.FSM_FLOW_ID = '000320'\n" +
                "  and (SELECT Y.ID\n" +
                "       FROM FSM_FLOW_INST X,\n" +
                "            FSM_STATE Y\n" +
                "       WHERE X.BUSINESS_KEY = A.ID\n" +
                "         AND X.CURRENT_STATE = Y.ID\n" +
                "         AND X.FSM_FLOW_ID = '000321'\n" +
                "         AND X.FINISHED = 0\n" +
                "         AND Y.STARTED = 0\n" +
                "         AND ROWNUM = 1) = :LABEL_TWO");
                if(param.containsKey("DEPT_ID")) sf.append("  and A.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }

    private void SQLCondition(StringBuffer sf,JSONObject param){
        String label_two = param.getString("LABEL_TWO");
        switch(label_two){
            case "000435": sf.append("  AND F.STATE NOT IN ('termination')\n");break;
            case "000434": sf.append("  AND F.STATE IN ('qualityTest')\n");break;
            default: sf.append("  AND F.STATE NOT IN ('termination','receivables')\n");break;
        }
    }

    /**
     * 未完结事项明细
     * -入参:DEPT_ID/ORDER_TYPE
     * @param param
     * @return
     */
    public JSONArray querySystemUseCheckOrderItem(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("select GET_PARTY_NAME(COMPANY_ID,'Company') company,\n" +
                "       (select NAME from MAS_DEPT D where D.ID = a.DEPT_ID ) dept,\n" +
                "       ORDER_TYPE,ID,to_char(CREATE_DATE,'yyyy-mm-dd hh24:mi:ss') CREATE_DATE,STATE_NAME,REMAKE,BUSINESS\n" +
                "from V_UNCOMPLETED_ORDER a");
        int whereFlag = 0;
        if (param.containsKey("DEPT_ID")){
            whereFlag = checkWhere(whereFlag,sf);
            sf.append(" a.dept_id = :DEPT_ID ");
        }
        if (param.containsKey("ORDER_TYPE")){
            whereFlag = checkWhere(whereFlag,sf);
            sf.append(" a.ORDER_TYPE = :ORDER_TYPE ");
        }
        return toJSONArray(find(sf.toString(),param));
    }

    /**
     * 判断是否需要加where/and
     * @param whereFlag
     * @param sf
     * @return
     */
    private int checkWhere(int whereFlag ,StringBuffer sf){
        if (whereFlag>0){
            sf.append(" and ");
            return 1;
        }
        sf.append(" where ");
       return ++whereFlag;
    }

    /**
     * 库存预警
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray stockWarring(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT K.LOCATION_ID,\n" +
                "       (SELECT NAME FROM MAS_LOCATION WHERE ID = K.LOCATION_ID) LOCATION_NAME,\n" +
                "       K.DEPT_ID,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = K.DEPT_ID)         DEPT_NAME,\n" +
                "       K.PRODUCT_ID,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PRODUCT\n" +
                "        WHERE ID = K.PRODUCT_ID)                                PRODUCT_NAME,\n" +
                "       K.USABLE_QTY,\n" +
                "       K.SAFETY_STOCK_QTY,\n" +
                "       K.USABLE_QTY - K.SAFETY_STOCK_QTY Gap,\n" +
                "       S.SALE_7,\n" +
                "       S.SALE_30\n" +
                "      FROM (SELECT A.LOCATION_ID,\n" +
                "                   B.DEPT_ID,\n" +
                "                   A.PRODUCT_ID,\n" +
                "                   A.USABLE_QTY,\n" +
                "                   A.SAFETY_STOCK_QTY\n" +
                "            FROM ACC_STOCK A,MAS_DEPOT B,MAS_LOCATION C\n" +
                "            WHERE A.LOCATION_ID = C.ID\n" +
                "              AND B.ID = C.DEPOT_ID) K,\n" +
                "           (SELECT B.DEPT_ID, A.PRODUCT_ID,\n" +
                "                   SUM(CASE WHEN DATE_DONE >= trunc(SYSDATE - 7) THEN A.PRODUCT_UOM_QTY\n" +
                "                           ELSE 0 END) sale_7,\n" +
                "                   SUM(CASE WHEN DATE_DONE >= trunc(SYSDATE - 30) THEN A.PRODUCT_UOM_QTY\n" +
                "                           ELSE 0 END) sale_30\n" +
                "                  FROM RETAIL_ORDER_ITEM A,RETAIL_ORDER B\n" +
                "           WHERE B.DATE_DONE >= trunc(SYSDATE - 30) AND A.SALE_ID = B.ID\n" +
                "               GROUP BY B.DEPT_ID, A.PRODUCT_ID) S\n" +
                "      WHERE K.DEPT_ID = S.DEPT_ID\n" +
                "        AND K.PRODUCT_ID = S.PRODUCT_ID\n" +
                "        AND K.USABLE_QTY < K.SAFETY_STOCK_QTY\n" +
                "        AND S.sale_30 >= 0");
                if (param.containsKey("DEPT_ID"))  sf.append(" AND K.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }

    /**
     * 滞销库存
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray unsalableStock(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT K.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT WHERE ID = K.DEPT_ID) DEPT_NAME,\n" +
                "        K.PRODUCT_ID,\n" +
                "        (SELECT NAME FROM MAS_PRODUCT WHERE ID = K.PRODUCT_ID) PRODUCT_NAME,\n" +
                "        K.USABLE_QTY,\n" +
                "        K.SAFETY_STOCK_QTY,\n" +
                "        nvl(S.SALE_7, 0) SALE_7,\n" +
                "        nvl(S.SALE_30, 0) SALE_30\n" +
                "       FROM (SELECT B.DEPT_ID,\n" +
                "                    A.PRODUCT_ID,\n" +
                "                    SUM(A.USABLE_QTY) USABLE_QTY,\n" +
                "                    SUM(A.SAFETY_STOCK_QTY) SAFETY_STOCK_QTY\n" +
                "             FROM ACC_STOCK A,MAS_DEPOT B,MAS_LOCATION C\n" +
                "             WHERE A.LOCATION_ID = C.ID\n" +
                "               AND B.ID = C.DEPOT_ID\n" +
                "           GROUP BY B.DEPT_ID,A.PRODUCT_ID) K\n" +
                "           LEFT JOIN\n" +
                "           (SELECT B.DEPT_ID, A.PRODUCT_ID,\n" +
                "                   SUM(CASE\n" +
                "                       WHEN DATE_DONE >= trunc(SYSDATE - 7) THEN A.PRODUCT_UOM_QTY\n" +
                "                       ELSE 0 END) sale_7,\n" +
                "                   SUM(CASE\n" +
                "                       WHEN DATE_DONE >= trunc(SYSDATE - 30) THEN A.PRODUCT_UOM_QTY\n" +
                "                       ELSE 0 END) sale_30\n" +
                "                 FROM RETAIL_ORDER_ITEM A,RETAIL_ORDER B\n" +
                "            WHERE A.SALE_ID = B.ID\n" +
                "               AND B.DATE_DONE >= trunc(SYSDATE - 30)\n" +
                "            GROUP BY B.DEPT_ID, A.PRODUCT_ID) S ON K.DEPT_ID = S.DEPT_ID AND K.PRODUCT_ID = S.PRODUCT_ID\n" +
                " WHERE K.USABLE_QTY > 0\n" +
                "   AND K.SAFETY_STOCK_QTY > 0\n" +
                "   AND (S.sale_30 IS NULL OR S.sale_30 <= 0)");
                if (param.containsKey("DEPT_ID"))  sf.append(" AND K.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }

    /**
     * 库存预警报表
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray stockWarringRCL(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT *\n" +
                " FROM RCL_STOCK_WARRING T \n");
        if (param.containsKey("DEPT_ID"))  sf.append(" WHERE T.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }

    /**
     * 滞销库存
     * -入参:DEPT_ID
     * @param param
     * @return
     */
    public JSONArray unsalableStockRCL(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT *\n" +
                " FROM RCL_UNSALABLE_STOCK T \n");
        if (param.containsKey("DEPT_ID"))  sf.append(" WHERE T.DEPT_ID = :DEPT_ID");
        return toJSONArray(find(sf.toString(),param));
    }

    /**
     * 待出方案(改)
     * @param param
     * @return
     */
    public JSONArray waitService(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT A.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       C.REALNAME,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = A.ID) type,\n" +
                "       A.MILEAGE,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from MAS_ENUMERATION f\n" +
                "         where f.id = A.fuel_meter) fuel_meter,\n" +
                "       A.key_number,\n" +
                "       (select F.NAME\n" +
                "          from MAS_PARTY_ROLE I, MAS_PARTY F\n" +
                "         where I.ID = A.CREATOR_ID\n" +
                "           and I.PARTY_ID = F.ID) || '/' ||\n" +
                "       TO_CHAR(A.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info\n" +
                "  FROM FN_PICK_UP_CAR A\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND C\n" +
                "    ON C.ID = A.FN_CUSTOMER_EXPAND_ID\n" +
                " WHERE A.STATE = 'approve'");
        if (param.containsKey("DEPT_ID")) sf.append(" AND A.DEPT_ID = :DEPT_ID");
        sf.append(" AND NOT EXISTS\n" +
                " (SELECT *\n" +
                "          FROM SER_SERVICE_PLAN B\n" +
                "         WHERE B.STATE NOT IN ('termination')\n" +
                "           AND B.confirm_date IS NOT NULL");
        if (param.containsKey("DEPT_ID")) sf.append(" AND B.BELONG_DEPT_ID = A.DEPT_ID");
        sf.append(" AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID)");
        return toJSONArray(find(sf.toString(),param));
    }


    /**
     * 待施工(改)
     * @param param
     * @return
     */
    public JSONArray waitWorkServicePlan(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT C.ID,\n" +
                "       H.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       J.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(C.STATE) STATE,\n" +
                "       I.NAME,\n" +
                "       F.SALE_QTY,\n" +
                "       F.PRICE_UNIT,\n" +
                "       F.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = C.BELONG_DEPT_ID) dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = H.ID) type,\n" +
                "       (select M.NAME\n" +
                "          from MAS_PARTY_ROLE N, MAS_PARTY M\n" +
                "         where N.ID = H.CREATOR_ID\n" +
                "           and N.PARTY_ID = M.ID) || '/' ||\n" +
                "       TO_CHAR(H.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(C.CREATE_DATE, 'yyyy-MM-dd HH:mm:ss') CREATE_DATE\n" +
                "  FROM SER_SERVICE_PLAN C\n" +
                "  LEFT JOIN SER_SERVICE_PLAN_ITEM F\n" +
                "    ON C.ID = F.SERVICE_PLAN_ID\n" +
                "  LEFT JOIN SER_MAINTENANCE_ORDER G\n" +
                "    ON G.SERVICE_PLAN_ITEM = F.ID\n" +
                "  LEFT JOIN FN_PICK_UP_CAR H\n" +
                "    ON H.FN_CUSTOMER_EXPAND_ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "   AND H.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND J\n" +
                "    ON J.ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "  LEFT JOIN MAS_PRODUCT I\n" +
                "    ON I.ID = F.PRODUCT_ID\n" +
                " WHERE C.STATE IN ('confirm')\n" +
                "   AND F.SERVICE_STATE = 1\n" +
                "   AND F.TERMINATION_DATE IS NULL ");
        if(param.containsKey("DEPT_ID")) sf.append(" AND C.BELONG_DEPT_ID = :DEPT_ID");
        sf.append(" AND G.WORK_START_TIME IS NULL\n" +
                "   AND H.STATE = 'approve'\n" +
                "   AND C.FN_CUSTOMER_EXPAND_ID IN\n" +
                "       (SELECT A.fn_customer_expand_id\n" +
                "          FROM FN_PICK_UP_CAR A\n" +
                "         WHERE A.STATE = 'approve'\n" +
                "           AND A.DEPT_ID = C.BELONG_DEPT_ID \n" +
                "           AND EXISTS\n" +
                "         (SELECT *\n" +
                "                  FROM SER_SERVICE_PLAN B\n" +
                "                 WHERE B.STATE NOT IN ('termination')\n" +
                "                   AND B.confirm_date IS NOT NULL\n" +
                "                   AND B.BELONG_DEPT_ID = A.DEPT_ID\n" +
                "                   AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID))");
        return toJSONArray(find(sf.toString(),param));
    }



    /**
     * 施工中(改)
     * @param param
     * @return
     */
    public JSONArray workingServicePlan(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT C.ID,\n" +
                "       H.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       J.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(C.STATE) STATE,\n" +
                "       I.NAME,\n" +
                "       F.SALE_QTY,\n" +
                "       F.PRICE_UNIT,\n" +
                "       F.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = C.BELONG_DEPT_ID) dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = H.ID) type,\n" +
                "       (select M.NAME\n" +
                "          from MAS_PARTY_ROLE N, MAS_PARTY M\n" +
                "         where N.ID = H.CREATOR_ID\n" +
                "           and N.PARTY_ID = M.ID) || '/' ||\n" +
                "       TO_CHAR(H.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(C.CREATE_DATE, 'yyyy-MM-dd HH:mm:ss') CREATE_DATE\n" +
                "  FROM SER_SERVICE_PLAN C\n" +
                "  LEFT JOIN SER_SERVICE_PLAN_ITEM F\n" +
                "    ON C.ID = F.SERVICE_PLAN_ID\n" +
                "  LEFT JOIN SER_MAINTENANCE_ORDER G\n" +
                "    ON G.SERVICE_PLAN_ITEM = F.ID\n" +
                "  LEFT JOIN FN_PICK_UP_CAR H\n" +
                "    ON H.FN_CUSTOMER_EXPAND_ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "   AND H.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND J\n" +
                "    ON J.ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "  LEFT JOIN MAS_PRODUCT I\n" +
                "    ON I.ID = F.PRODUCT_ID\n" +
                " WHERE C.STATE IN ('confirm')\n" +
                "   AND F.SERVICE_STATE = 1\n" +
                "   AND F.TERMINATION_DATE IS NULL ");
        if (param.containsKey("DEPT_ID")) sf.append(" AND C.BELONG_DEPT_ID = :DEPT_ID");
        sf.append(" AND G.WORK_STATE = 'working'\n" +
                "   AND H.STATE = 'approve'\n" +
                "   AND C.FN_CUSTOMER_EXPAND_ID IN\n" +
                "       (SELECT A.fn_customer_expand_id\n" +
                "          FROM FN_PICK_UP_CAR A\n" +
                "         WHERE A.STATE = 'approve'\n" +
                "           AND A.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "           AND EXISTS\n" +
                "         (SELECT *\n" +
                "                  FROM SER_SERVICE_PLAN B\n" +
                "                 WHERE B.STATE NOT IN ('termination')\n" +
                "                   AND B.confirm_date IS NOT NULL\n" +
                "                   AND B.BELONG_DEPT_ID = A.Dept_Id\n" +
                "                   AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID))");
        return toJSONArray(find(sf.toString(),param));
    }


    /**
     * 待质检(改)
     * @param param
     * @return
     */
    public JSONArray waitCheckServicePlan(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT C.ID,\n" +
                "       H.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       J.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(C.STATE) STATE,\n" +
                "       I.NAME,\n" +
                "       F.SALE_QTY,\n" +
                "       F.PRICE_UNIT,\n" +
                "       F.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = C.BELONG_DEPT_ID) dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = H.ID) type,\n" +
                "       (select M.NAME\n" +
                "          from MAS_PARTY_ROLE N, MAS_PARTY M\n" +
                "         where N.ID = H.CREATOR_ID\n" +
                "           and N.PARTY_ID = M.ID) || '/' ||\n" +
                "       TO_CHAR(H.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(C.CREATE_DATE, 'yyyy-MM-dd HH:mm:ss') CREATE_DATE\n" +
                "  FROM SER_SERVICE_PLAN C\n" +
                "  LEFT JOIN SER_SERVICE_PLAN_ITEM F\n" +
                "    ON C.ID = F.SERVICE_PLAN_ID\n" +
                "  LEFT JOIN SER_MAINTENANCE_ORDER G\n" +
                "    ON G.SERVICE_PLAN_ITEM = F.ID\n" +
                "  LEFT JOIN FN_PICK_UP_CAR H\n" +
                "    ON H.FN_CUSTOMER_EXPAND_ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "   AND H.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND J\n" +
                "    ON J.ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "  LEFT JOIN MAS_PRODUCT I\n" +
                "    ON I.ID = F.PRODUCT_ID\n" +
                " WHERE C.STATE IN ('confirm')\n" +
                "   AND F.SERVICE_STATE = 1\n" +
                "   AND F.TERMINATION_DATE IS NULL ");
        if (param.containsKey("DEPT_ID")) sf.append(" AND C.BELONG_DEPT_ID = :DEPT_ID");
        sf.append(" AND G.WORK_STATE = 'finish'\n" +
                "   AND H.STATE = 'approve'\n" +
                "   AND C.FN_CUSTOMER_EXPAND_ID IN\n" +
                "       (SELECT A.fn_customer_expand_id\n" +
                "          FROM FN_PICK_UP_CAR A\n" +
                "         WHERE A.STATE = 'approve'\n" +
                "           AND A.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "           AND EXISTS\n" +
                "         (SELECT *\n" +
                "                  FROM SER_SERVICE_PLAN B\n" +
                "                 WHERE B.STATE NOT IN ('termination')\n" +
                "                   AND B.confirm_date IS NOT NULL\n" +
                "                   AND B.BELONG_DEPT_ID = A.Dept_Id\n" +
                "                   AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID))");
        return toJSONArray(find(sf.toString(),param));
    }


    /**
     * 待收款(改)
     * @param param
     * @return
     */
    public JSONArray waitReceivablesServicePlan(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT C.ID,\n" +
                "       H.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       J.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(C.STATE) STATE,\n" +
                "       I.NAME,\n" +
                "       F.SALE_QTY,\n" +
                "       F.PRICE_UNIT,\n" +
                "       F.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = C.BELONG_DEPT_ID) dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = H.ID) type,\n" +
                "       (select M.NAME\n" +
                "          from MAS_PARTY_ROLE N, MAS_PARTY M\n" +
                "         where N.ID = H.CREATOR_ID\n" +
                "           and N.PARTY_ID = M.ID) || '/' ||\n" +
                "       TO_CHAR(H.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(C.CREATE_DATE, 'yyyy-MM-dd HH:mm:ss') CREATE_DATE\n" +
                "  FROM SER_SERVICE_PLAN C\n" +
                "  LEFT JOIN SER_SERVICE_PLAN_ITEM F\n" +
                "    ON C.ID = F.SERVICE_PLAN_ID\n" +
                "  LEFT JOIN SER_MAINTENANCE_ORDER G\n" +
                "    ON G.SERVICE_PLAN_ITEM = F.ID\n" +
                "  LEFT JOIN FN_PICK_UP_CAR H\n" +
                "    ON H.FN_CUSTOMER_EXPAND_ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "   AND H.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND J\n" +
                "    ON J.ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "  LEFT JOIN MAS_PRODUCT I\n" +
                "    ON I.ID = F.PRODUCT_ID\n" +
                " WHERE C.STATE IN ('qualityTest')\n" +
                "   AND F.SERVICE_STATE = 1\n" +
                "   AND F.TERMINATION_DATE IS NULL ");
        if (param.containsKey("DEPT_ID")) sf.append(" AND C.BELONG_DEPT_ID = :DEPT_ID");
        sf.append(" AND G.WORK_STATE = 'finish'\n" +
                "   AND H.STATE = 'approve'\n" +
                "   AND C.FN_CUSTOMER_EXPAND_ID IN\n" +
                "       (SELECT A.fn_customer_expand_id\n" +
                "          FROM FN_PICK_UP_CAR A\n" +
                "         WHERE A.STATE = 'approve'\n" +
                "           AND A.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "           AND EXISTS\n" +
                "         (SELECT *\n" +
                "                  FROM SER_SERVICE_PLAN B\n" +
                "                 WHERE B.STATE NOT IN ('termination')\n" +
                "                   AND B.confirm_date IS NOT NULL\n" +
                "                   AND B.BELONG_DEPT_ID = A.Dept_Id \n" +
                "                   AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID))");
        return toJSONArray(find(sf.toString(),param));
    }


    /**
     * 待交车(改)
     * @param param
     * @return
     */
    public JSONArray waitStillCarServicePlan(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT C.ID,\n" +
                "       H.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       J.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(C.STATE) STATE,\n" +
                "       I.NAME,\n" +
                "       F.SALE_QTY,\n" +
                "       F.PRICE_UNIT,\n" +
                "       F.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = C.BELONG_DEPT_ID) dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = H.ID) type,\n" +
                "       (select M.NAME\n" +
                "          from MAS_PARTY_ROLE N, MAS_PARTY M\n" +
                "         where N.ID = H.CREATOR_ID\n" +
                "           and N.PARTY_ID = M.ID) || '/' ||\n" +
                "       TO_CHAR(H.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(C.CREATE_DATE, 'yyyy-MM-dd HH:mm:ss') CREATE_DATE\n" +
                "  FROM SER_SERVICE_PLAN C\n" +
                "  LEFT JOIN SER_SERVICE_PLAN_ITEM F\n" +
                "    ON C.ID = F.SERVICE_PLAN_ID\n" +
                "  LEFT JOIN SER_MAINTENANCE_ORDER G\n" +
                "    ON G.SERVICE_PLAN_ITEM = F.ID\n" +
                "  LEFT JOIN FN_PICK_UP_CAR H\n" +
                "    ON H.FN_CUSTOMER_EXPAND_ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "   AND H.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND J\n" +
                "    ON J.ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "  LEFT JOIN MAS_PRODUCT I\n" +
                "    ON I.ID = F.PRODUCT_ID\n" +
                " WHERE C.STATE IN ('receivables')\n" +
                "   AND F.SERVICE_STATE = 1\n" +
                "   AND F.TERMINATION_DATE IS NULL ");
        if(param.containsKey("DEPT_ID")) sf.append(" AND C.BELONG_DEPT_ID = :DEPT_ID");
        sf.append(" AND H.STATE = 'approve'\n" +
                "   AND C.FN_CUSTOMER_EXPAND_ID IN\n" +
                "       (SELECT A.fn_customer_expand_id\n" +
                "          FROM FN_PICK_UP_CAR A\n" +
                "         WHERE A.STATE = 'approve'\n" +
                "           AND A.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "           AND NOT EXISTS\n" +
                "         (SELECT *\n" +
                "                  FROM SER_SERVICE_PLAN B\n" +
                "                 WHERE B.STATE NOT IN ('receivables','termination') \n" +
                "                   AND B.BELONG_DEPT_ID = A.Dept_Id\n" +
                "                   AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID))");
        return toJSONArray(find(sf.toString(),param));
    }


    /**
     * 未结算交车(改)
     * @param param
     * @return
     */
    public JSONArray unReceivablesStillCarServicePlan(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT C.ID,\n" +
                "       H.LICENSE_NUMBER CAR_LICENSE,\n" +
                "       J.REALNAME,\n" +
                "       GET_SERVICEPLAN_STATE(C.STATE) STATE,\n" +
                "       I.NAME,\n" +
                "       F.SALE_QTY,\n" +
                "       F.PRICE_UNIT,\n" +
                "       F.SALES_AMOUNT,\n" +
                "       (select i.NAME from MAS_DEPT i where i.ID = C.BELONG_DEPT_ID) dept_name,\n" +
                "       (select f.DESCRIPTION\n" +
                "          from fn_pick_up_type i, mas_enumeration f\n" +
                "         where i.TYPE = f.ID\n" +
                "           and i.PICK_UP_CAR_ID = H.ID) type,\n" +
                "       (select M.NAME\n" +
                "          from MAS_PARTY_ROLE N, MAS_PARTY M\n" +
                "         where N.ID = H.CREATOR_ID\n" +
                "           and N.PARTY_ID = M.ID) || '/' ||\n" +
                "       TO_CHAR(H.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') get_car_info,\n" +
                "       to_char(C.CREATE_DATE, 'yyyy-MM-dd HH:mm:ss') CREATE_DATE\n" +
                "  FROM SER_SERVICE_PLAN C\n" +
                "  LEFT JOIN SER_SERVICE_PLAN_ITEM F\n" +
                "    ON C.ID = F.SERVICE_PLAN_ID\n" +
                "  LEFT JOIN SER_MAINTENANCE_ORDER G\n" +
                "    ON G.SERVICE_PLAN_ITEM = F.ID\n" +
                "  LEFT JOIN FN_PICK_UP_CAR H\n" +
                "    ON H.FN_CUSTOMER_EXPAND_ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "   AND H.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "  LEFT JOIN FN_CUSTOMER_EXPAND J\n" +
                "    ON J.ID = C.FN_CUSTOMER_EXPAND_ID\n" +
                "  LEFT JOIN MAS_PRODUCT I\n" +
                "    ON I.ID = F.PRODUCT_ID\n" +
                " WHERE C.STATE IN ('qualityTest')\n" +
                "   AND F.SERVICE_STATE = 1\n" +
                "   AND F.TERMINATION_DATE IS NULL ");
        if (param.containsKey("DEPT_ID")) sf.append(" AND C.BELONG_DEPT_ID = :DEPT_ID");
        sf.append(" AND H.STATE != 'approve'\n" +
                "   AND C.FN_CUSTOMER_EXPAND_ID IN\n" +
                "       (SELECT A.fn_customer_expand_id\n" +
                "          FROM FN_PICK_UP_CAR A\n" +
                "         WHERE A.STATE != 'approve'\n" +
                "           AND A.DEPT_ID = C.BELONG_DEPT_ID\n" +
                "           AND EXISTS\n" +
                "         (SELECT *\n" +
                "                  FROM SER_SERVICE_PLAN B\n" +
                "                 WHERE B.STATE IN ('qualityTest') \n" +
                "                   AND B.BELONG_DEPT_ID = A.Dept_Id\n" +
                "                   AND B.FN_CUSTOMER_EXPAND_ID = A.FN_CUSTOMER_EXPAND_ID))");
        return toJSONArray(find(sf.toString(),param));
    }
}
